package Dao;

import DB.DBConnection;
import pojo.MilkTeaProduct;
import java.sql.*;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.*;

public class ReportDao {

    public static final DateTimeFormatter DATE_FORMAT = DateTimeFormatter.ofPattern("yyyy-MM-dd");

    public Map<String, Object> generateDailySalesReport() {
        return generateSalesReportForDate(java.time.LocalDate.now());
    }

    private Map<String, Object> generateSalesReportForDate(LocalDate date) {
        String formattedDate = DATE_FORMAT.format(date);
        Map<String, Object> report = new HashMap<>();
        report.put("date", formattedDate);
        report.put("total_sales", getTotalSales(formattedDate));
        report.put("product_sales", getProductSales(formattedDate));
        report.put("orders_count", getOrdersCount(formattedDate));
        report.put("profit", getProfit(formattedDate));
        return report;
    }


    private double getTotalSales(String date) {
        double totalSales = 0.0;
        String sql = "SELECT SUM(total_amount) as total FROM order_management WHERE DATE(created_at) = ?";
        try (Connection connection = DBConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setString(1, date);
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next()) {
                totalSales = resultSet.getDouble("total");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return totalSales;
    }

    private Map<MilkTeaProduct, Integer> getProductSales(String date) {
        Map<MilkTeaProduct, Integer> productSales = new HashMap<>();
        String sql = "SELECT mt.product_id, mt.product_name, SUM(oi.quantity) as total_sold " +
                "FROM order_items oi " +
                "JOIN milk_tea mt ON oi.product_id = mt.product_id " +
                "JOIN order_management om ON om.order_id = oi.order_id " +
                "WHERE DATE(om.created_at) = ? " +
                "GROUP BY mt.product_id, mt.product_name";
        try (Connection connection = DBConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setString(1, date);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                int productId = resultSet.getInt("product_id");
                String productName = resultSet.getString("product_name");
                int totalSold = resultSet.getInt("total_sold");
                MilkTeaProduct product = new MilkTeaProduct(productId, productName, 0, 0, null);
                productSales.put(product, totalSold);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return productSales;
    }

    private int getOrdersCount(String date) {
        int ordersCount = 0;
        String sql = "SELECT COUNT(*) as count FROM order_management WHERE DATE(created_at) = ?";
        try (Connection connection = DBConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setString(1, date);
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next()) {
                ordersCount = resultSet.getInt("count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ordersCount;
    }

    private double getTotalCost(String date) {
        double totalCost = 0.0;
        String sql = "SELECT SUM(oi.quantity * IFNULL(mt.inprice, 0)) as total_cost " +
                "FROM order_items oi " +
                "JOIN milk_tea mt ON oi.product_id = mt.product_id " +
                "JOIN order_management om ON om.order_id = oi.order_id " +
                "WHERE DATE(om.created_at) = ?";
        try (Connection connection = DBConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setString(1, date);
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next()) {
                totalCost = resultSet.getDouble("total_cost");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return totalCost;
    }

    private double getProfit(String date) {
        double totalSales = getTotalSales(date);
        double totalCost = getTotalCost(date);
        double profit = totalSales - totalCost;
        return profit;
    }
}